
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
-- --------------------------------------------------
-- Date Created: 12/26/2011 15:10:06
-- Generated from EDMX file: D:\Documentos\msFinance\myFinance\Model\Context.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [myFinance];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[FK_PessoaConta]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Conta] DROP CONSTRAINT [FK_PessoaConta];
GO
IF OBJECT_ID(N'[dbo].[FK_ContaPagamento]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Pagamento] DROP CONSTRAINT [FK_ContaPagamento];
GO
IF OBJECT_ID(N'[dbo].[FK_TarefasConta]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Conta] DROP CONSTRAINT [FK_TarefasConta];
GO
IF OBJECT_ID(N'[dbo].[FK_TarefasProjeto]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Tarefa] DROP CONSTRAINT [FK_TarefasProjeto];
GO
IF OBJECT_ID(N'[dbo].[FK_ProjetoPessoa]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Projeto] DROP CONSTRAINT [FK_ProjetoPessoa];
GO
IF OBJECT_ID(N'[dbo].[FK_AgendaCompromisso]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Compromisso] DROP CONSTRAINT [FK_AgendaCompromisso];
GO
IF OBJECT_ID(N'[dbo].[FK_TarefaPessoa]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Tarefa] DROP CONSTRAINT [FK_TarefaPessoa];
GO
IF OBJECT_ID(N'[dbo].[FK_PessoaAgenda]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Agenda] DROP CONSTRAINT [FK_PessoaAgenda];
GO
IF OBJECT_ID(N'[dbo].[FK_PessoaCompromisso]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Compromisso] DROP CONSTRAINT [FK_PessoaCompromisso];
GO
IF OBJECT_ID(N'[dbo].[FK_GrupoDespesaSubGrupoDespesa]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[SubGrupoDespesa] DROP CONSTRAINT [FK_GrupoDespesaSubGrupoDespesa];
GO
IF OBJECT_ID(N'[dbo].[FK_PessoaGrupoDespesa]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[GrupoDespesa] DROP CONSTRAINT [FK_PessoaGrupoDespesa];
GO
IF OBJECT_ID(N'[dbo].[FK_ContaConta]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Conta] DROP CONSTRAINT [FK_ContaConta];
GO
IF OBJECT_ID(N'[dbo].[FK_ContaGrupoDespesa]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Conta] DROP CONSTRAINT [FK_ContaGrupoDespesa];
GO
IF OBJECT_ID(N'[dbo].[FK_ContaSubGrupoDespesa]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Conta] DROP CONSTRAINT [FK_ContaSubGrupoDespesa];
GO

-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[Pessoa]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Pessoa];
GO
IF OBJECT_ID(N'[dbo].[Conta]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Conta];
GO
IF OBJECT_ID(N'[dbo].[Pagamento]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Pagamento];
GO
IF OBJECT_ID(N'[dbo].[Projeto]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Projeto];
GO
IF OBJECT_ID(N'[dbo].[Tarefa]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Tarefa];
GO
IF OBJECT_ID(N'[dbo].[Compromisso]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Compromisso];
GO
IF OBJECT_ID(N'[dbo].[Agenda]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Agenda];
GO
IF OBJECT_ID(N'[dbo].[GrupoDespesa]', 'U') IS NOT NULL
    DROP TABLE [dbo].[GrupoDespesa];
GO
IF OBJECT_ID(N'[dbo].[SubGrupoDespesa]', 'U') IS NOT NULL
    DROP TABLE [dbo].[SubGrupoDespesa];
GO

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'Pessoa'
CREATE TABLE [dbo].[Pessoa] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Nome] nvarchar(150)  NOT NULL,
    [DataNascimento] datetime  NOT NULL,
    [CEP] nvarchar(9)  NULL,
    [Endereco] nvarchar(150)  NULL,
    [Numero] int  NULL,
    [Bairro] nvarchar(100)  NULL,
    [Complemento] nvarchar(100)  NULL,
    [FoneResidencial] nvarchar(14)  NULL,
    [FoneComercial] nvarchar(14)  NULL,
    [FoneCelular] nvarchar(14)  NULL,
    [Email] nvarchar(100)  NOT NULL,
    [Twitter] nvarchar(30)  NULL,
    [Msn] nvarchar(100)  NULL,
    [Site] nvarchar(100)  NULL,
    [DataCadastro] datetime  NOT NULL,
    [Usuario] nvarchar(30)  NOT NULL,
    [RG] nvarchar(15)  NULL,
    [CPF] nvarchar(14)  NULL,
    [Avatar] varbinary(max)  NULL,
    [Cidade] nvarchar(150)  NULL,
    [UF] nvarchar(2)  NULL,
    [Sexo] bit  NULL,
    [facebook_userID] int  NULL
);
GO

-- Creating table 'Conta'
CREATE TABLE [dbo].[Conta] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [DataCadastro] datetime  NOT NULL,
    [DataVencimento] datetime  NOT NULL,
    [Descricao] nvarchar(100)  NOT NULL,
    [Observacao] nvarchar(300)  NULL,
    [Valor] decimal(8,2)  NOT NULL,
    [PessoaId] int  NOT NULL,
    [Parcelas] int  NOT NULL,
    [Quitada] bit  NOT NULL,
    [Entrada] smallint  NOT NULL,
    [CodigoReferencia] int  NOT NULL,
    [IdContaPrincipal] int  NULL,
    [GerarTarefa] bit  NOT NULL,
    [GrupoDespesaId] int  NOT NULL,
    [SubGrupoDespesaId] int  NOT NULL,
    [Parcelada] bit  NOT NULL,
    [Parcela] int  NOT NULL,
    [Tarefa_Id] int  NULL,
    [Conta_ContaPrincipal_Id] int  NULL
);
GO

-- Creating table 'Pagamento'
CREATE TABLE [dbo].[Pagamento] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [DataCadastro] datetime  NOT NULL,
    [DataPagamento] datetime  NOT NULL,
    [ValorPago] decimal(8,2)  NOT NULL,
    [Juros] decimal(8,2)  NULL,
    [Desconto] decimal(8,2)  NULL,
    [Observacao] nvarchar(300)  NULL,
    [FormaPagamento] nvarchar(50)  NOT NULL,
    [Conta_Id] int  NOT NULL
);
GO

-- Creating table 'Projeto'
CREATE TABLE [dbo].[Projeto] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Nome] nvarchar(100)  NOT NULL,
    [DataCadastro] datetime  NOT NULL,
    [DataPrevisaoInicio] datetime  NOT NULL,
    [DataPrevisaoFim] datetime  NOT NULL,
    [DataInicio] datetime  NULL,
    [DataFim] datetime  NULL,
    [Descricao] nvarchar(300)  NULL,
    [PessoaId] int  NOT NULL
);
GO

-- Creating table 'Tarefa'
CREATE TABLE [dbo].[Tarefa] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Nome] nvarchar(100)  NOT NULL,
    [DataCadastro] datetime  NOT NULL,
    [DataTarefa] datetime  NOT NULL,
    [Descricao] nvarchar(max)  NULL,
    [Concluida] bit  NOT NULL,
    [ProjetoId] int  NULL,
    [AvisarPorEmail] bit  NOT NULL,
    [AvisarPorPopUp] bit  NOT NULL,
    [PessoaId] int  NOT NULL,
    [HoraInicio] time  NOT NULL,
    [HoraFim] time  NOT NULL,
    [Codigo] int  NOT NULL
);
GO

-- Creating table 'Compromisso'
CREATE TABLE [dbo].[Compromisso] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Nome] nvarchar(100)  NOT NULL,
    [DataInicio] datetime  NOT NULL,
    [DataFim] datetime  NOT NULL,
    [Local] nvarchar(300)  NULL,
    [Descricao] nvarchar(300)  NOT NULL,
    [AvisarPorEmail] bit  NOT NULL,
    [AvisarPorPopUp] bit  NOT NULL,
    [AgendaId] int  NOT NULL,
    [PessoaId] int  NOT NULL,
    [HoraInicio] time  NOT NULL,
    [HoraFim] time  NOT NULL
);
GO

-- Creating table 'Agenda'
CREATE TABLE [dbo].[Agenda] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Nome] nvarchar(100)  NOT NULL,
    [Descricao] nvarchar(300)  NULL,
    [DataCadastro] datetime  NOT NULL,
    [PessoaId] int  NOT NULL
);
GO

-- Creating table 'GrupoDespesa'
CREATE TABLE [dbo].[GrupoDespesa] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Nome] nvarchar(max)  NOT NULL,
    [PessoaId] int  NOT NULL
);
GO

-- Creating table 'SubGrupoDespesa'
CREATE TABLE [dbo].[SubGrupoDespesa] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Nome] nvarchar(max)  NOT NULL,
    [GrupoDespesaId] int  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [Id] in table 'Pessoa'
ALTER TABLE [dbo].[Pessoa]
ADD CONSTRAINT [PK_Pessoa]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Conta'
ALTER TABLE [dbo].[Conta]
ADD CONSTRAINT [PK_Conta]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Pagamento'
ALTER TABLE [dbo].[Pagamento]
ADD CONSTRAINT [PK_Pagamento]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Projeto'
ALTER TABLE [dbo].[Projeto]
ADD CONSTRAINT [PK_Projeto]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Tarefa'
ALTER TABLE [dbo].[Tarefa]
ADD CONSTRAINT [PK_Tarefa]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Compromisso'
ALTER TABLE [dbo].[Compromisso]
ADD CONSTRAINT [PK_Compromisso]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Agenda'
ALTER TABLE [dbo].[Agenda]
ADD CONSTRAINT [PK_Agenda]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'GrupoDespesa'
ALTER TABLE [dbo].[GrupoDespesa]
ADD CONSTRAINT [PK_GrupoDespesa]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'SubGrupoDespesa'
ALTER TABLE [dbo].[SubGrupoDespesa]
ADD CONSTRAINT [PK_SubGrupoDespesa]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [PessoaId] in table 'Conta'
ALTER TABLE [dbo].[Conta]
ADD CONSTRAINT [FK_PessoaConta]
    FOREIGN KEY ([PessoaId])
    REFERENCES [dbo].[Pessoa]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_PessoaConta'
CREATE INDEX [IX_FK_PessoaConta]
ON [dbo].[Conta]
    ([PessoaId]);
GO

-- Creating foreign key on [Conta_Id] in table 'Pagamento'
ALTER TABLE [dbo].[Pagamento]
ADD CONSTRAINT [FK_ContaPagamento]
    FOREIGN KEY ([Conta_Id])
    REFERENCES [dbo].[Conta]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ContaPagamento'
CREATE INDEX [IX_FK_ContaPagamento]
ON [dbo].[Pagamento]
    ([Conta_Id]);
GO

-- Creating foreign key on [Tarefa_Id] in table 'Conta'
ALTER TABLE [dbo].[Conta]
ADD CONSTRAINT [FK_TarefasConta]
    FOREIGN KEY ([Tarefa_Id])
    REFERENCES [dbo].[Tarefa]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_TarefasConta'
CREATE INDEX [IX_FK_TarefasConta]
ON [dbo].[Conta]
    ([Tarefa_Id]);
GO

-- Creating foreign key on [ProjetoId] in table 'Tarefa'
ALTER TABLE [dbo].[Tarefa]
ADD CONSTRAINT [FK_TarefasProjeto]
    FOREIGN KEY ([ProjetoId])
    REFERENCES [dbo].[Projeto]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_TarefasProjeto'
CREATE INDEX [IX_FK_TarefasProjeto]
ON [dbo].[Tarefa]
    ([ProjetoId]);
GO

-- Creating foreign key on [PessoaId] in table 'Projeto'
ALTER TABLE [dbo].[Projeto]
ADD CONSTRAINT [FK_ProjetoPessoa]
    FOREIGN KEY ([PessoaId])
    REFERENCES [dbo].[Pessoa]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ProjetoPessoa'
CREATE INDEX [IX_FK_ProjetoPessoa]
ON [dbo].[Projeto]
    ([PessoaId]);
GO

-- Creating foreign key on [AgendaId] in table 'Compromisso'
ALTER TABLE [dbo].[Compromisso]
ADD CONSTRAINT [FK_AgendaCompromisso]
    FOREIGN KEY ([AgendaId])
    REFERENCES [dbo].[Agenda]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_AgendaCompromisso'
CREATE INDEX [IX_FK_AgendaCompromisso]
ON [dbo].[Compromisso]
    ([AgendaId]);
GO

-- Creating foreign key on [PessoaId] in table 'Tarefa'
ALTER TABLE [dbo].[Tarefa]
ADD CONSTRAINT [FK_TarefaPessoa]
    FOREIGN KEY ([PessoaId])
    REFERENCES [dbo].[Pessoa]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_TarefaPessoa'
CREATE INDEX [IX_FK_TarefaPessoa]
ON [dbo].[Tarefa]
    ([PessoaId]);
GO

-- Creating foreign key on [PessoaId] in table 'Agenda'
ALTER TABLE [dbo].[Agenda]
ADD CONSTRAINT [FK_PessoaAgenda]
    FOREIGN KEY ([PessoaId])
    REFERENCES [dbo].[Pessoa]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_PessoaAgenda'
CREATE INDEX [IX_FK_PessoaAgenda]
ON [dbo].[Agenda]
    ([PessoaId]);
GO

-- Creating foreign key on [PessoaId] in table 'Compromisso'
ALTER TABLE [dbo].[Compromisso]
ADD CONSTRAINT [FK_PessoaCompromisso]
    FOREIGN KEY ([PessoaId])
    REFERENCES [dbo].[Pessoa]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_PessoaCompromisso'
CREATE INDEX [IX_FK_PessoaCompromisso]
ON [dbo].[Compromisso]
    ([PessoaId]);
GO

-- Creating foreign key on [GrupoDespesaId] in table 'SubGrupoDespesa'
ALTER TABLE [dbo].[SubGrupoDespesa]
ADD CONSTRAINT [FK_GrupoDespesaSubGrupoDespesa]
    FOREIGN KEY ([GrupoDespesaId])
    REFERENCES [dbo].[GrupoDespesa]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_GrupoDespesaSubGrupoDespesa'
CREATE INDEX [IX_FK_GrupoDespesaSubGrupoDespesa]
ON [dbo].[SubGrupoDespesa]
    ([GrupoDespesaId]);
GO

-- Creating foreign key on [PessoaId] in table 'GrupoDespesa'
ALTER TABLE [dbo].[GrupoDespesa]
ADD CONSTRAINT [FK_PessoaGrupoDespesa]
    FOREIGN KEY ([PessoaId])
    REFERENCES [dbo].[Pessoa]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_PessoaGrupoDespesa'
CREATE INDEX [IX_FK_PessoaGrupoDespesa]
ON [dbo].[GrupoDespesa]
    ([PessoaId]);
GO

-- Creating foreign key on [Conta_ContaPrincipal_Id] in table 'Conta'
ALTER TABLE [dbo].[Conta]
ADD CONSTRAINT [FK_ContaConta]
    FOREIGN KEY ([Conta_ContaPrincipal_Id])
    REFERENCES [dbo].[Conta]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ContaConta'
CREATE INDEX [IX_FK_ContaConta]
ON [dbo].[Conta]
    ([Conta_ContaPrincipal_Id]);
GO

-- Creating foreign key on [GrupoDespesaId] in table 'Conta'
ALTER TABLE [dbo].[Conta]
ADD CONSTRAINT [FK_ContaGrupoDespesa]
    FOREIGN KEY ([GrupoDespesaId])
    REFERENCES [dbo].[GrupoDespesa]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ContaGrupoDespesa'
CREATE INDEX [IX_FK_ContaGrupoDespesa]
ON [dbo].[Conta]
    ([GrupoDespesaId]);
GO

-- Creating foreign key on [SubGrupoDespesaId] in table 'Conta'
ALTER TABLE [dbo].[Conta]
ADD CONSTRAINT [FK_ContaSubGrupoDespesa]
    FOREIGN KEY ([SubGrupoDespesaId])
    REFERENCES [dbo].[SubGrupoDespesa]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ContaSubGrupoDespesa'
CREATE INDEX [IX_FK_ContaSubGrupoDespesa]
ON [dbo].[Conta]
    ([SubGrupoDespesaId]);
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------